*******************************************************************************************
*******************************************************************************************
******************** Reclassification Risk in the Small Group Health Insurance Market
******************* by Sebastian Fleitas, Gautam Gowrisankaran and Anthony Lo Sasso 
*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
******************** Table B1 Panel A 
*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
 


cd "/Users/sebastianfleitas/Dropbox/ReclassificationRisk"
use database_individual_level.dta, replace 


**********************************************************************************************
**********************************************************************************************
******************** DESCRIPTIVE STATISTICS FOR FINAL SAMPLE 
**********************************************************************************************
**********************************************************************************************

/* 
xtset mbr_sys_id year 

gen lagged_code_hypertension = L.code_hypertension 
gen lagged_code_heartfailure = L.code_heartfailure 
gen lagged_code_chronickidneydisease = L.code_chronickidneydisease 
gen lagged_code_atrialfibrilization= L.code_atrialfibrilization 
gen lagged_code_asthma =L.code_asthma

gen lagged_chronic_cancer     = L.code_cancer
gen lagged_chronic_transplant = L.code_transplant
gen lagged_chronic_ami        = L.code_ami
gen lagged_chronic_diabetes   = L.code_diabetes

gen lagged_code_brainhemorr = L.code_brainhemorr
gen laggeed_code_respfailure = L.code_respfailure


****************************************************
****** CREATING Rp BEFORE DROPPING DATA 2012 ******* 
****************************************************

bys customer_number  year        : egen mean_pred_riskscore_rp_aux = mean(acg_riskscore)
xtset mbr_sys_id year
gen mean_pred_riskscore_rp = L.mean_pred_riskscore_rp_aux

bys customer_number  year        : egen mean_ORS_riskscore_rp_aux = mean(ORS_riskscore)
xtset mbr_sys_id year
gen mean_ORS_riskscore_rp = L.mean_ORS_riskscore_rp_aux

bys prime_plan_code  year        : egen mean_pred_policy_rp_aux = mean(acg_riskscore)
xtset mbr_sys_id year
gen mean_pred_policy_rp = L.mean_pred_policy_rp_aux

bys prime_plan_code  year        : egen mean_ORS_policy_rp_aux = mean(ORS_riskscore)
xtset mbr_sys_id year
gen mean_ORS_policy_rp  = L.mean_ORS_policy_rp_aux
****************************************************
****************************************************



****************************************************

****************************************************
/*
****** FOR USIC other database STUDY******* 
****************************************************
bys customer_number  year        : egen mean_pred_USIC_rp_aux = mean(usic_score)
xtset mbr_sys_id year
gen mean_pred_USIC_rp =L.mean_pred_USIC_rp_aux
*/
****************************************************

*exit


****************************************************
**** Other variables 
****************************************************
gen laggedclaims = L.paid_sum
gen laggedoutpocket = L.outpocket
gen laggedallowed_sum = L.allowed_sum

drop if  firmstays!=1
drop if year==2012
tab rel_cd , gen(relationship)

egen in2012 = total(laggedclaims!=. & year==2013), by(mbr_sys_id)
egen in2013 = total(year==2013), by(mbr_sys_id)
egen in2014 = total(year==2014), by(mbr_sys_id)
egen in2015 = total(year==2015), by(mbr_sys_id)
gen only2013 = (in2015!=1 & in2014!=1 & in2013==1 & in2012!=1)
gen only2014 = (in2015!=1 & in2014==1 & in2013!=1 & in2012!=1)
gen only2015 = (in2015==1 & in2014!=1 & in2013!=1 & in2012!=1)
gen exjoiners_201314no2012 =      (in2015!=1 & in2014==1 & in2013==1 & in2012!=1)
gen exquitters_201213no2014 =     (in2015!=1 & in2014!=1 & in2013==1 & in2012==1)
gen exstayers_20121314no2015 =    (in2015!=1 & in2014==1 & in2013==1 & in2012==1)
gen newjoiners2013_stayers2015 =  (in2015==1 & in2014==1 & in2013==1 & in2012!=1)
gen newjoiners2014 =              (in2015==1 & in2014==1 & in2013!=1 & in2012!=1)
gen newstayers_allyears =         (in2015==1 & in2014==1 & in2013==1 & in2012==1)
gen everyone_onlyoneyear_aux = only2013 + only2014 + only2015
gen nocathegory = 1- everyone_onlyoneyear_aux - exjoiners_201314no2012 - exquitters_201213no2014 - exstayers_20121314no2015 - newjoiners2013_stayers2015 - newjoiners2014 - newstayers_allyears
gen everyone_onlyoneyear_scattered = nocathegory + everyone_onlyoneyear_aux

************************************************************
***** Generate Stayers, Joiners and Quitters 
************************************************************
* case no joiner, no quitter no stayer mbr_sys_id==231551255

* aux variables 
bys mbr_sys_id: egen max_year= max(year)
bys mbr_sys_id: egen min_year= min(year)
gen complete_obs_aux = 1 if laggedscore!=.
gen no_2013_completeObs =  1 if year==2013 & complete_obs_aux!=1 
bys mbr_sys_id: egen complete_obs = max(complete_obs_aux)
** define joiner 
gen joiner_aux = 1 if (min_year!=2013 & complete_obs==1) | (min_year==2013 & no_2013_completeObs==1 & complete_obs==1)
bys mbr_sys_id: egen joiner = max(joiner_aux)
** define quitter
gen quitter  = 1 if max_year!=2015 & complete_obs==1


********** TABLE DESCRIPTIVE STATISTICS 
egen ind = group(mbr_sys_id) if newstayers_allyears==1 | joiner == 1 | quitter == 1
egen ind_stayers = group(mbr_sys_id)  if newstayers_allyears==1
egen ind_joiners = group(mbr_sys_id)  if joiner == 1
egen ind_quitters = group(mbr_sys_id) if quitter == 1
sum ind ind_* 


******* CREATE AUTOMATIC TABLE 
  foreach Y in lagged_chronic_cancer lagged_chronic_ami lagged_chronic_transplant lagged_chronic_diabetes lagged_code_hypertension lagged_code_heartfailure  lagged_code_asthma {
  gen `Y'_100 = `Y'*100
  }
gen chronickidneydisease_100 = lagged_code_chronickidneydisease*100

gen employee_100 = (relationship2==1)
gen spouse_100 = (relationship6==1)
gen children_100 = (relationship1==1)
gen others_100 =    ( relationship3==1 | relationship4==1 | relationship5==1)
gen female_100 = female*100

*/

xtset mbr_sys_id year

qui {
  *complete below with all variables 
  foreach X in  employee_100 spouse_100 children_100 others_100 age female_100 paid_sum  outpocket allowed_sum  av_premiumpc_plan  laggedscore D.laggedscore lagged_chronic_cancer_100 lagged_chronic_ami_100 lagged_chronic_transplant_100 lagged_chronic_diabetes_100 lagged_code_hypertension_100 lagged_code_heartfailure_100 chronickidneydisease_100 lagged_code_asthma_100 {

 		
	/*
	
	else if "`X'"=="employee_100" | "`X'"=="spouse_100" | "`X'"=="children_100" | "`X'"=="others_100" {
	if "`X'"=="employee_100" noi dis      "Relation ($\%$) & & & & \\"
	if "`X'"=="employee_100" noi dis _con "\hspace{0.1in} Employees"
	if "`X'"=="spouse_100" noi dis _con "\hspace{0.1in} Spouses"
	if "`X'"=="children_100" noi dis _con "\hspace{0.1in} Children"
	if "`X'"=="others_100" noi dis _con "\hspace{0.1in} Others"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
	local mean1 = r(mean)*100
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if newstayers_allyears==1 
	local mean1 = r(mean)*100
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if joiner == 1
	local mean1 = r(mean)*100
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if quitter == 1
    local mean2 = r(mean)*100
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	
	else if "`X'"=="age"  {
	if "`X'"=="age" noi dis _con "Age"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.0f")
	local val21=string(`sd1',"%8.0f")
    noi dis _con "& $`val11'$ ($`val21'$) "
	sum `X' if newstayers_allyears==1 
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if joiner==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if quitter== 1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis "& $`val12'$ ($`val22'$) \\ "
	}
	
	
	else if "`X'"=="female_100" {
	if "`X'"=="female_100" noi dis _con "Female (\%)"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if newstayers_allyears==1 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if joiner== 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if  quitter== 1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.0f")
    noi dis "& $`val12'$  \\ \hline "
	}

	
	else if "`X'"=="paid_sum" | "`X'"=="outpocket" | "`X'"=="av_premiumpc_plan" 	{
	if "`X'"=="paid_sum" noi dis  "In dollars: & & & & \\"
	if "`X'"=="paid_sum" noi dis _con "Lagged paid total claims "
	if "`X'"=="outpocket" noi dis _con "Lagged out-of-pocket claims "
	if "`X'"=="av_premiumpc_plan" noi dis _con "Mean annual premiums"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.0fc")
	local val21=string(`sd1',"%8.0fc")
    noi dis _con "& `val11' (`val21') "
	sum `X' if newstayers_allyears==1 
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis _con "& `val12' (`val22')  "
	sum `X' if joiner== 1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis _con "& `val12' (`val22')  "
	sum `X' if quitter== 1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis "& `val12' (`val22') \\   "
	}
	
	  
	else if "`X'"=="laggedscore" | "`X'"=="D.laggedscore" {
	if "`X'"=="laggedscore" noi dis _con " \hline $ r $"
	if "`X'"=="D.laggedscore" noi dis _con " $\Delta r $"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.2fc")
	local val21=string(`sd1',"%8.2fc")
    noi dis _con "& $`val11'$ ($`val21'$) "
	sum `X' if newstayers_allyears==1 
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if joiner== 1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if quitter== 1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis "& $`val12'$ ($`val22'$) \\   "
	}

	
	else if "`X'"=="lagged_chronic_cancer_100"  { 
	if "`X'"=="lagged_chronic_cancer_100" noi dis  "\hline Conditions ($\%$) & & & & \\"
	if "`X'"=="lagged_chronic_cancer_100" noi dis _con "Cancer"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if newstayers_allyears==1 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if joiner== 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if quitter== 1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	*/
	
	
	else if "`X'"=="lagged_chronic_ami_100" | "`X'"=="lagged_chronic_transplant_100" | "`X'"=="lagged_chronic_diabetes_100" | "`X'"=="lagged_code_hypertension_100" | "`X'"=="lagged_code_heartfailure_100" | "`X'"=="chronickidneydisease_100" |  "`X'"=="lagged_code_asthma_100" { 
	if "`X'"=="lagged_chronic_ami_100" noi dis _con "Acute myocardial infarction"
	if "`X'"=="lagged_chronic_transplant_100" noi dis _con "Transplant"
	if "`X'"=="lagged_chronic_diabetes_100" noi dis _con "Diabetes"
	if "`X'"=="lagged_code_hypertension_100" noi dis _con "Hypertension"
	if "`X'"=="lagged_code_heartfailure_100" noi dis _con "Heart disease"
	if "`X'"=="chronickidneydisease_100" noi dis _con "Chronic kidney disease"
	if "`X'"=="lagged_code_asthma_100" noi dis _con "Asthma"
	sum `X' if newstayers_allyears==1 | joiner == 1 | quitter == 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if newstayers_allyears==1 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if joiner== 1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if  quitter== 1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	
  }
}

